#!/usr/bin/env bash

############################################################################
# File Name: postgresql_install.sh
# Version: V1.0
# Author: Brian Hsiung
# Created Time : 2025-05-19 14:30:00
# Description: PostgreSQL 14.18,15.13,16.9,17.5安装部署脚本
# kGbMnkqR!RcE.2DwsgcHvw
###########################################################################

# set -e # Exit immediately if a command exits with a non-zero status.

# 版本号
declare -r PG_VERSION=14.18
# 运行用户
declare -r PG_USER=postgres
# 运行端口
declare -r PG_PORT=5432
# 默认为single，表示单机模式，如果需要配置主从，需要设置为master或slave
# master 表示主节点
# slave 表示从节点
declare -r PG_NODE_ROLE="single"
# 如果要配置主从，设置以下变量(根据实际情况修改)
declare -r PG_MASTER_HOST="172.26.85.156"
declare -r PG_SLAVE_HOST="172.26.85.157"
# 数据目录的上级目录
declare -r PG_DATA_BASE=/data/pgsql
# postgres的密码
declare -r PG_POSTGRES_PASSWORD="u5kMfh!h6Y9.jkWjAFwipC"
# repl_user的密码
declare -r PG_REPL_USER_PASSWORD="kMfh!h6Y9.jkWjAFwipC"
# 管理员用户, 用于所有数据库和角色的日常管理
declare -r PG_ADMIN_USER=admin
# 管理员密码, 用于所有数据库和角色的日常管理
declare -r PG_ADMIN_PASSWORD="kMh!h6Y9.jk1jAfwipC"
# 最大连接数
declare -r PG_MAX_CONNECTIONS=3000
# 编译安装路径
declare -r PG_HOME=$PG_DATA_BASE/pgsql${PG_VERSION}
# 软连接路径，便于升级
declare -r PG_HOME_LINK=/usr/local/pgsql
# 日志目录
declare -r PG_LOG_DIR=$PG_DATA_BASE/log
# 数据目录
declare -r PG_DATA_DIR=$PG_DATA_BASE/data
# 备份目录
declare -r PG_BACK_DIR=$PG_DATA_BASE/backups
# 归档目录
declare -r PG_ARCHIVE_DIR=$PG_DATA_BASE/archives
# 阿里云访问地址
declare -r OSS_BASE_URL="https://brianhsiung.oss-cn-hangzhou.aliyuncs.com"

WORK_DIR="$(cd "$(dirname "$0")" && pwd)"
cd "$WORK_DIR" || exit

##############################################
# 日志函数
##############################################
log_info()    { echo -e "\033[34m[INFO] $*\033[0m"; }
log_warn()    { echo -e "\033[33m[WARNING] $*\033[0m"; }
log_success() { echo -e "\033[32m[SUCCESS] $*\033[0m"; }
log_error()   { echo -e "\033[31m[ERROR] $*\033[0m"; exit 1; }

##############################################
# 检查依赖
##############################################
check_dependency() {

    local -a required_cmds=("wget" "tar" "groupadd" "useradd" "gcc" "make")
    local missing=()

    log_info "检查依赖命令: ${required_cmds[*]}"
    for cmd in "${required_cmds[@]}"; do
        if ! command -v "$cmd" &>/dev/null; then
            missing+=("$cmd")
        fi
    done

    ((${#missing[@]})) && log_error "缺失依赖命令: ${missing[*]}"
}
##############################################
# 检查PostgreSQL存储目录
##############################################
check_pgsql_dir() {
    local pgsql_dir=$1
    if [ ! -d "$pgsql_dir" ]; then
        log_info "创建目录: $pgsql_dir"
        mkdir -p "$pgsql_dir"
        chmod 700 "$pgsql_dir"
        chown -R $PG_USER:$PG_USER "$pgsql_dir"
    elif [ "$(ls -A "$pgsql_dir")" ]; then
        log_error "目录 $pgsql_dir 存在且不为空!"
    fi
}
##############################################
# 设置目录权限（递归检查上级目录）
##############################################
set_parent_permissions() {
    ### 某些等保三服务器默认新建的的目录权限为700，导致服务无法启动，所以需要遍历上级目录，将权限修改为755
    # 获取真实目录
    local target_dir
    target_dir=$(realpath "$1")
    # 获取上级目录
    local current_dir
    current_dir=$(dirname "$target_dir")

    # 做个判断，避免进入死循环
    [ -d "$target_dir" ] || log_error "目录不存在 [$target_dir]"

    while [[ "$current_dir" != "/" ]]; do
        # 跳过不存在的目录
        if [[ ! -d "$current_dir" ]]; then
            log_warn "警告: 目录不存在 [$current_dir]"
            current_dir=$(dirname "$current_dir")
            continue
        fi
        
        # 获取当前目录权限
        local perm_oct
        perm_oct=$(stat -c '%a' "$current_dir")
        
        if [[ "$perm_oct" != "755" ]]; then
            log_info "正在设置权限: [755] -> $current_dir"
            if ! chmod 755 "$current_dir"; then
                log_error "错误: 无法设置目录权限 [$current_dir]"
            fi
        else
            log_success "权限正确 [755]: $current_dir"
        fi

        # 向上一级目录
        current_dir=$(dirname "$current_dir")
    done
}
##############################################
# 初始化环境
##############################################
init_env() {
    [ "$EUID" -ne 0 ] && log_error "请使用root账号执行此脚本！"

    if ss -tuln | grep -q ":$PG_PORT "; then
        log_error "端口 $PG_PORT 被占用，请检查服务是否已经存在"
    fi

    if systemctl is-active --quiet firewalld; then
        firewall-cmd --zone=public --add-port="$PG_PORT"/tcp --permanent
        firewall-cmd --reload
    fi

    if ! getent group "$PG_USER" >/dev/null 2>&1; then
        log_info "创建用户组: $PG_USER"
        groupadd "$PG_USER"
    fi

    if ! getent passwd "$PG_USER" >/dev/null 2>&1; then
        log_info "创建用户: $PG_USER"
        useradd -g "$PG_USER" "$PG_USER"
    fi

    if [ ! -d "$PG_DATA_BASE" ]; then
        log_info "创建目录: $PG_DATA_BASE"
        mkdir -p "$PG_DATA_BASE"
        chmod 700 "$PG_DATA_BASE"
        chown -R "$PG_USER:$PG_USER" "$PG_DATA_BASE"
        set_parent_permissions "$PG_DATA_BASE"
    fi
    check_pgsql_dir "$PG_LOG_DIR"
    check_pgsql_dir "$PG_DATA_DIR"
    check_pgsql_dir "$PG_ARCHIVE_DIR"
    check_pgsql_dir "$PG_BACK_DIR"

    local sysctl_conf="/etc/sysctl.conf"
    local sysctl_params=(
        "net.core.somaxconn = 4096"
        "net.ipv4.tcp_max_syn_backlog = 8192"
        "vm.swappiness = 0"
    )

    log_info "设置系统参数: $sysctl_conf"
    /bin/cp -af /etc/sysctl.conf "/etc/sysctl.conf.bak.$(date '+%Y%m%d%H%M%S')"

    for param in "${sysctl_params[@]}"; do
        if ! grep -q "$param" "$sysctl_conf"; then
            echo "$param" >> "$sysctl_conf"
            log_info "添加参数: $param"
        else
            log_success "参数已存在: $param"
        fi
    done
    sysctl -p

    # 透明大页设置
    log_info "透明大页设置"
    tee /sys/kernel/mm/transparent_hugepage/{enabled,defrag} <<< never
    # 开机启动配置
    grep -q 'transparent_hugepage' /etc/rc.d/rc.local || cat << EOF >> /etc/rc.d/rc.local
echo never > /sys/kernel/mm/transparent_hugepage/enabled
echo never > /sys/kernel/mm/transparent_hugepage/defrag
EOF
    chmod +x /etc/rc.d/rc.local

    # 文件描述符限制
    local limits_conf="/etc/security/limits.conf"
    if ! grep -q "root.*nofile" "${limits_conf}"; then
        log_info "设置文件描述符限制"
        cat << EOF >> "${limits_conf}"
root soft nofile 65535
root hard nofile 65535
* soft nofile 65535
* hard nofile 65535
EOF
    fi

}

##############################################
# 编译安装PostgreSQL
##############################################
install_postgresql() {
    local cmd
    if command -v dnf >/dev/null 2>&1; then
        cmd="dnf"
    elif command -v yum >/dev/null 2>&1; then
        cmd="yum"
    else
        log_error "不支持的系统"
    fi

    log_info "安装依赖"
    # python3-devel (华为的操作系统... bison flex perl-devel)
    # libicu libicu-devel 版本16、17需要
    $cmd install -y perl-ExtUtils-Embed readline-devel zlib zlib-devel lz4 bison flex perl-devel openssl openssl-devel

    if [ ! -f postgresql-${PG_VERSION}.tar.gz ] \
        &&  curl --output /dev/null --silent --head --fail --connect-timeout 5 --max-time 10 "$OSS_BASE_URL"/health; then
        log_info "下载 PostgreSQL ${PG_VERSION} 源码..."
        wget -q "$OSS_BASE_URL/deploy/postgresql/postgresql-${PG_VERSION}.tar.gz" || log_error "下载失败"
    fi

    [ -f "postgresql-${PG_VERSION}.tar.gz" ] || log_error "当前目录下未找到 postgresql-${PG_VERSION}.tar.gz"

    tar -zxf postgresql-${PG_VERSION}.tar.gz
    cd postgresql-${PG_VERSION} || exit
    ./configure --prefix=$PG_HOME --with-perl --with-python
    make -j4 && make install
    cd contrib && make && make install
    chmod 755 -R $PG_HOME  # 某些服务器默认权限为700

    if [ -L "$PG_HOME_LINK" ]; then
        log_info "检测到 $PG_HOME_LINK 软链接，即将更新指向..."
        ln -sfn  $PG_HOME $PG_HOME_LINK
    elif [ -e "$PG_HOME_LINK" ]; then
        log_error "$PG_HOME_LINK 已存在，无法创建软链接"
    else
        log_info "创建软链接 $PG_HOME --> $PG_HOME_LINK"
        ln -s $PG_HOME $PG_HOME_LINK
    fi

    find "$PG_HOME"/bin -type f -exec chmod 755 {} \;
    
    if [ "$PG_NODE_ROLE" = "single" ]; then
        log_info "当前主机为单机点，初始化数据库..."
        su - "$PG_USER" -c "$PG_HOME_LINK/bin/initdb -D $PG_DATA_DIR"
    elif [ "$PG_NODE_ROLE" = "master" ]; then
        log_info "当前主机为主节点，初始化数据库..."
        su - "$PG_USER" -c "$PG_HOME_LINK/bin/initdb -D $PG_DATA_DIR"
    fi

}

##############################################
# 添加服务
##############################################
install_pg_service() {

    local major_version
    major_version=$(echo "$PG_VERSION" | cut -d. -f1)

    cat > /usr/lib/systemd/system/postgresql.service <<EOF
[Unit]
Description=PostgreSQL ${major_version} database server
Documentation=https://www.postgresql.org/docs/${major_version}/static/
After=syslog.target
After=network-online.target

[Service]
Type=forking

User=$PG_USER
Group=$PG_USER

# Location of database directory
Environment=PGDATA=$PG_DATA_DIR

# Where to send early-startup messages from the server (before the logging
# options of postgresql.conf take effect)
# This is normally controlled by the global default set by systemd
# StandardOutput=syslog

# Disable OOM kill on the postmaster
OOMScoreAdjust=-1000
Environment=PG_OOM_ADJUST_FILE=/proc/self/oom_score_adj
Environment=PG_OOM_ADJUST_VALUE=0

# -l 日志, -s silent, -w wait, -t 超时时间
ExecStart=$PG_HOME_LINK/bin/pg_ctl start -D \${PGDATA} -s -w -t 300
ExecStop=$PG_HOME_LINK/bin/pg_ctl stop -D \${PGDATA} -s -m fast
ExecReload=$PG_HOME_LINK/bin/pg_ctl reload -D \${PGDATA} -s
KillMode=mixed
KillSignal=SIGINT
 
# Do not set any timeout value, so that systemd will not kill postmaster
# during crash recovery.
TimeoutSec=0

# 0 is the same as infinity, but "infinity" needs systemd 229
TimeoutStartSec=0

TimeoutStopSec=1h

[Install]
WantedBy=multi-user.target
EOF

    systemctl daemon-reload
    systemctl enable postgresql

    if [ "$PG_NODE_ROLE" = "single" ]; then
        log_info "当前节点是单节点，启动PostgreSQL服务"
        systemctl start postgresql
        log_info "设置postgres的密码"
        su - "$PG_USER" -c "$PG_HOME_LINK/bin/psql -c \"ALTER USER postgres WITH PASSWORD '$PG_POSTGRES_PASSWORD';\""
        # 创建一个可以创建角色，数据库的用户，用于所有数据库和角色的日常管理
        su - "$PG_USER" -c "$PG_HOME_LINK/bin/psql -c \"CREATE USER $PG_ADMIN_USER WITH PASSWORD '$PG_ADMIN_PASSWORD' CREATEROLE CREATEDB;\""
    elif [ "$PG_NODE_ROLE" = "master" ]; then
        log_info "当前节点是主节点，启动PostgreSQL服务"
        systemctl start postgresql
        log_info "创建主从复制账号"
        su - "$PG_USER" -c "$PG_HOME_LINK/bin/psql -c \"CREATE ROLE repl_user REPLICATION LOGIN PASSWORD '$PG_REPL_USER_PASSWORD';\""
        # 创建一个可以创建角色，数据库的用户，用于所有数据库和角色的日常管理
        su - "$PG_USER" -c "$PG_HOME_LINK/bin/psql -c \"CREATE USER $PG_ADMIN_USER WITH PASSWORD '$PG_ADMIN_PASSWORD' CREATEROLE CREATEDB;\""
        log_info "设置postgres的密码"
        su - "$PG_USER" -c "$PG_HOME_LINK/bin/psql -c \"ALTER USER postgres WITH PASSWORD '$PG_POSTGRES_PASSWORD';\""
    fi
}

config_pg_conf() {
    if [ -f "$PG_DATA_DIR"/postgresql.conf ]; then
        log_info "备份配置文件postgresql.conf"
        /bin/cp -af "$PG_DATA_DIR"/postgresql.conf "$PG_DATA_DIR"/postgresql.conf.bak_"$(date +%Y%m%d%H%M%S)"
    fi
    if [ -f "$PG_DATA_DIR"/pg_hba.conf ]; then
        log_info "备份配置文件pg_hba.conf"
        /bin/cp -af "$PG_DATA_DIR"/pg_hba.conf "$PG_DATA_DIR"/pg_hba.conf.bak_"$(date +%Y%m%d%H%M%S)"
    fi
    
    if [ "$PG_NODE_ROLE" = "single" ]; then
        cat > "$PG_DATA_DIR"/postgresql.conf << EOF
listen_addresses = '*'
port = 5432
max_connections = $PG_MAX_CONNECTIONS
# 可配置为物理内存的25～40%
shared_buffers = 256MB
dynamic_shared_memory_type = posix
max_wal_size = 1GB
min_wal_size = 80MB
log_destination = 'stderr'
logging_collector = on
log_directory = '$PG_LOG_DIR'
log_filename = 'postgresql-%a.log'
log_rotation_age = 1d 
log_rotation_size = 0
log_truncate_on_rotation = on 
log_line_prefix = '%m [%p] '
log_timezone = 'Asia/Shanghai'
datestyle = 'iso, mdy'
timezone = 'Asia/Shanghai'
lc_messages = 'en_US.UTF-8'
lc_monetary = 'en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'
default_text_search_config = 'pg_catalog.english'
# 默认的密码策略
password_encryption = scram-sha-256
# 单个查询可用的内存建议
work_mem = 4MB
# 数据库可用的总缓存
effective_cache_size = 4GB
# 并行查询的线程数，根据CPU核心数调整
max_parallel_workers_per_gather = 2
# 用于VACUUM、索引创建等维护操作的内存
maintenance_work_mem = 64MB
# 自动清理死元组的关键参数
autovacuum_max_workers = 3
# 设置为-1，根据shared_buffers的值自动调整
wal_buffers = -1
# 开启归档(若要解析WAL为SQL语句,设置为logical)
wal_level = replica
archive_mode = on
# 归档文件路径
archive_command = 'test ! -f $PG_ARCHIVE_DIR/%f && cp %p $PG_ARCHIVE_DIR/%f'
EOF

        # 允许远程连接，连接时需要提供密码
        echo "host    all             all             0.0.0.0/0               scram-sha-256" >> "$PG_DATA_DIR"/pg_hba.conf
        log_info "重启PostgreSQL服务"
        systemctl restart postgresql

    elif [ "$PG_NODE_ROLE" = "master" ]; then
        cat > "$PG_DATA_DIR"/postgresql.conf << EOF
listen_addresses = '*'
port = 5432
max_connections = 2000
# 可配置为物理内存的25～40%
shared_buffers = 256MB
dynamic_shared_memory_type = posix
max_wal_size = 1GB
min_wal_size = 80MB
log_destination = 'stderr'
logging_collector = on
log_directory = '$PG_LOG_DIR'
log_filename = 'postgresql-%a.log'
log_rotation_age = 1d 
log_rotation_size = 0
log_truncate_on_rotation = on
log_line_prefix = '%m [%p] '
log_timezone = 'Asia/Shanghai'
datestyle = 'iso, mdy'
timezone = 'Asia/Shanghai'
lc_messages = 'en_US.UTF-8'
lc_monetary = 'en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'
default_text_search_config = 'pg_catalog.english'
# 默认的密码策略
password_encryption = scram-sha-256
# 单个查询可用的内存建议
work_mem = 4MB
# 数据库可用的总缓存
effective_cache_size = 4GB
# 并行查询的线程数，根据CPU核心数调整
max_parallel_workers_per_gather = 2
# 用于VACUUM、索引创建等维护操作的内存
maintenance_work_mem = 64MB
# 自动清理死元组的关键参数
autovacuum_max_workers = 3
# 设置为-1，根据shared_buffers的值自动调整
wal_buffers = -1
# 开启归档(若要解析WAL为SQL语句,设置为logical)
wal_level = replica
archive_mode = on
max_wal_senders = 10
wal_keep_size = 128MB
# 归档文件路径
archive_command = 'test ! -f $PG_ARCHIVE_DIR/%f && cp %p $PG_ARCHIVE_DIR/%f'
EOF

        # 允许远程连接，连接时需要提供密码
        echo "host    all             all             0.0.0.0/0               scram-sha-256" >> "$PG_DATA_DIR"/pg_hba.conf
        echo "host    replication     repl_user       $PG_SLAVE_HOST/32       scram-sha-256" >> "$PG_DATA_DIR"/pg_hba.conf
        log_info "重启PostgreSQL服务"
        systemctl restart postgresql
    fi
}
# https://www.cnblogs.com/jiaxzeng/p/17934554.html
# https://www.cnblogs.com/wangchengshi/p/18110288
# https://yanue.net/post-180.html
# https://wiki.sqlfans.cn/postgresql/pg-ha-replication.html
# https://www.alibabacloud.com/help/zh/ecs/use-cases/build-a-primary-or-secondary-postgresql-architecture#30e326fb83fge
init_replication() {
    if [ "$PG_NODE_ROLE" = "slave" ]; then
        log_info "设置数据库主从复制"
        while :
        do
            su - "$PG_USER" -c "export PGPASSWORD=$PG_POSTGRES_PASSWORD && $PG_HOME_LINK/bin/psql -h $PG_MASTER_HOST -p $PG_PORT -U postgres -c \"SELECT 1;\"" \
                && log_success "主库可用" && break
            log_warn "等待主库可用..."
            sleep 10
        done

        su - "$PG_USER" -c "export PGPASSWORD=$PG_REPL_USER_PASSWORD && $PG_HOME_LINK/bin/pg_basebackup \
            -h $PG_MASTER_HOST -p $PG_PORT -D $PG_DATA_DIR -U repl_user -P -w -v --wal-method=stream"
        
        if [ -f "$PG_DATA_DIR"/postgresql.conf ]; then
            log_info "备份配置文件postgresql.conf"
            /bin/cp -af "$PG_DATA_DIR"/postgresql.conf "$PG_DATA_DIR"/postgresql.conf.bak_"$(date +%Y%m%d%H%M%S)"
        fi
        if [ -f "$PG_DATA_DIR"/pg_hba.conf ]; then
            log_info "备份配置文件pg_hba.conf"
            /bin/cp -af "$PG_DATA_DIR"/pg_hba.conf "$PG_DATA_DIR"/pg_hba.conf.bak_"$(date +%Y%m%d%H%M%S)"
        fi

        # 从主库备份过来的配置文件
        cat > "$PG_DATA_DIR"/postgresql.conf << EOF
listen_addresses = '*'
port = 5432
max_connections = 2000
# 可配置为物理内存的25～40%
shared_buffers = 256MB
dynamic_shared_memory_type = posix
max_wal_size = 1GB
min_wal_size = 80MB
log_destination = 'stderr'
logging_collector = on
log_directory = '$PG_LOG_DIR'
log_filename = 'postgresql-%a.log'
log_rotation_age = 1d 
log_rotation_size = 0
log_truncate_on_rotation = on
log_line_prefix = '%m [%p] '
log_timezone = 'Asia/Shanghai'
datestyle = 'iso, mdy'
timezone = 'Asia/Shanghai'
lc_messages = 'en_US.UTF-8'
lc_monetary = 'en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'
default_text_search_config = 'pg_catalog.english'
# 默认的密码策略
password_encryption = scram-sha-256
# 单个查询可用的内存建议
work_mem = 4MB
# 数据库可用的总缓存
effective_cache_size = 4GB
# 并行查询的线程数，根据CPU核心数调整
max_parallel_workers_per_gather = 2
# 用于VACUUM、索引创建等维护操作的内存
maintenance_work_mem = 64MB
# 自动清理死元组的关键参数
autovacuum_max_workers = 3
# 设置为-1，根据shared_buffers的值自动调整
wal_buffers = -1
# 开启归档(若要解析WAL为SQL语句,设置为logical)
wal_level = replica
archive_mode = on
max_wal_senders = 10
wal_keep_size = 128MB
# 归档文件路径
archive_command = 'test ! -f $PG_ARCHIVE_DIR/%f && cp %p $PG_ARCHIVE_DIR/%f'
######################################################## 从库设置 ########################################################
# 开启备用服务器的只读模式
hot_standby = on
max_standby_streaming_delay = 30s
# 控制 walreceiver 进程向主服务器发送心跳消息的时间间隔
wal_receiver_status_interval = 5s
# 如果有错误的数据复制向主进行反馈
hot_standby_feedback = on
# 主库连接信息
primary_conninfo = 'host=$PG_MASTER_HOST port=$PG_PORT user=repl_user password=$PG_REPL_USER_PASSWORD'
EOF
        # 设置从库可以接管主库
        [ -f "$PG_DATA_DIR/standby.signal" ] || su - "$PG_USER" -c "touch $PG_DATA_DIR/standby.signal"
        # echo 'standby_mode = on' | tee -a $PG_DATA_DIR/standby.signal
        log_info "开始启动 PostgreSQL ${PG_VERSION}..."
        systemctl start postgresql
    fi
}

main() {
    log_info "开始安装 PostgreSQL ${PG_VERSION}..."
    check_dependency
    init_env
    install_postgresql
    install_pg_service
    config_pg_conf
    # 从库slave配置
    init_replication
    log_success "PostgreSQL ${PG_VERSION} 安装完成!"
}

main